package com.CommunityHub.kiana.dao;



import com.CommunityHub.kiana.po.ResidenceInfo;
import com.CommunityHub.kiana.utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ResidenceInfoDAO {
    // 根据会员ID查询入住人
    public List<ResidenceInfo> findByMemberId(int memberId) throws SQLException {
        List<ResidenceInfo> occupants = new ArrayList<>();
        String sql = "SELECT * FROM residence_info WHERE member_id = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, memberId);
            rs = ps.executeQuery();

            while (rs.next()) {
                ResidenceInfo occupant = mapResidenceInfo(rs);
                occupants.add(occupant);
            }
            return occupants;
        } finally {
            DBUtil.close(conn, ps, rs);
        }
    }

    // 根据房屋ID查询入住人
    public List<ResidenceInfo> findByHouseId(int houseId) throws SQLException {
        List<ResidenceInfo> occupants = new ArrayList<>();
        String sql = "SELECT * FROM residence_info WHERE house_id = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, houseId);
            rs = ps.executeQuery();

            while (rs.next()) {
                ResidenceInfo occupant = mapResidenceInfo(rs);
                occupants.add(occupant);
            }
            return occupants;
        } finally {
            DBUtil.close(conn, ps, rs);
        }
    }

    // 添加入住人
    public boolean addOccupant(ResidenceInfo occupant) throws SQLException {
        String sql = "INSERT INTO residence_info (house_id, member_id, occupant_name, relationship, "
                + "id_card, age, gender, start_time, residence_type) "
                + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, occupant.getHouseId());
            ps.setInt(2, occupant.getMemberId());
            ps.setString(3, occupant.getOccupantName());
            ps.setString(4, occupant.getRelationship());
            ps.setString(5, occupant.getIdCard());
            ps.setInt(6, occupant.getAge());
            ps.setString(7, occupant.getGender());
            ps.setTimestamp(8, new java.sql.Timestamp(occupant.getStartTime().getTime()));
            ps.setString(9, occupant.getResidenceType());

            int rowsAffected = ps.executeUpdate();
            return rowsAffected > 0;
        } finally {
            DBUtil.close(conn, ps, null);
        }
    }

    private ResidenceInfo mapResidenceInfo(ResultSet rs) throws SQLException {
        ResidenceInfo occupant = new ResidenceInfo();
        occupant.setResidenceId(rs.getInt("residence_id"));
        occupant.setHouseId(rs.getInt("house_id"));
        occupant.setMemberId(rs.getInt("member_id"));
        occupant.setOccupantName(rs.getString("occupant_name"));
        occupant.setRelationship(rs.getString("relationship"));
        occupant.setIdCard(rs.getString("id_card"));
        occupant.setAge(rs.getInt("age"));
        occupant.setGender(rs.getString("gender"));
        occupant.setStartTime(rs.getTimestamp("start_time"));
        occupant.setEndTime(rs.getTimestamp("end_time"));
        occupant.setResidenceType(rs.getString("residence_type"));
        return occupant;
    }
    public ResidenceInfo findById(int residenceId) throws SQLException {
        String sql = "SELECT * FROM residence_info WHERE residence_id = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, residenceId);
            rs = ps.executeQuery();

            if (rs.next()) {
                return mapResidenceInfo(rs);
            }
            return null;
        } finally {
            DBUtil.close(conn, ps, rs);
        }
    }

    // 更新入住人信息
    public boolean updateOccupant(ResidenceInfo info) throws SQLException {
        String sql = "UPDATE residence_info SET occupant_name=?, relationship=?, "
                + "age=?, gender=?, start_time=?, end_time=?, residence_type=? "
                + "WHERE residence_id=?";

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);

            ps.setString(1, info.getOccupantName());
            ps.setString(2, info.getRelationship());
            ps.setInt(3, info.getAge());
            ps.setString(4, info.getGender());
            ps.setTimestamp(5, info.getStartTime());
            ps.setTimestamp(6, info.getEndTime());
            ps.setString(7, info.getResidenceType());
            ps.setInt(8, info.getResidenceId());

            int rowsAffected = ps.executeUpdate();
            System.out.println("执行SQL：" + ps.toString());
            System.out.println("影响行数：" + rowsAffected);
            return rowsAffected > 0;

        } finally {
            DBUtil.close(conn, ps, null);
        }
    }

    public ResidenceInfo getOccupantById(int residenceId) throws SQLException {
        String sql = "SELECT * FROM residence_info WHERE residence_id=?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, residenceId);
            rs = ps.executeQuery();

            if (rs.next()) {
                ResidenceInfo info = new ResidenceInfo();
                info.setResidenceId(rs.getInt("residence_id"));
                info.setOccupantName(rs.getString("occupant_name"));
                info.setRelationship(rs.getString("relationship"));
                info.setAge(rs.getInt("age"));
                info.setGender(rs.getString("gender"));
                info.setStartTime(rs.getTimestamp("start_time"));
                info.setEndTime(rs.getTimestamp("end_time"));
                info.setResidenceType(rs.getString("residence_type"));
                return info;
            }
            return null;
        } finally {
            DBUtil.close(conn, ps, rs);
        }
    }

}
